/*
 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 U P G R A D E  D A T A B A S E  F O R  F U N D  L E V E L  S E C U R I T Y 
 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 Description:  
 This script will make metadata changes to the database in order to support fund level security.  It
 will then upgrade the data to fill in the new columns.

 Prequisites
 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/

Set nocount on

Begin Try

/*
C h e c k  i f  d a t a b a s e  a l r e a d y  u p g r a d e d !
Determine if the database is already upgraded  by looking for the FwkResourceId column in table
AgrFund.  If it does not exist, assume that the database is not upgraded and execute the upgrade script.
Otherwise skip the upgrade.
*/
if NOT exists(select top 1 fwkResourceId from dbo.agrfund where fwkResourceId is not null)
begin
	/* 
	>>>>>>>>>>>>>>>>>>>>>>>>>
	B e g i n  U p g r a d e
	>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	print ''
	print 'Fund Level Security Upgrade Starting...'


	Begin Transaction FundLevelSecurityDBUpgrade

	/*	A d d  r e s o u r c e  t y p e  =  f u n d 
		Do ONLY if not there already! 
	*/
	If not exists(select 1 from dbo.fwkresourcetype (nolock) where name='Fund') 
	Begin
		print ''
		print 'Inserting fwkresourcetype 4 = Fund...'
		insert into dbo.fwkresourcetype(Id,[name],[description]) values (4,'Fund','A resource of type fund')
	End
	--Obtain the fund resource type id. for later use
	
	Declare @FundResourceTypeId int
	select @FundResourceTypeId = id from dbo.fwkresourcetype (nolock) where [name]='Fund'
	/*
	A s s i g n  e a c h  f u n d  a  F w k R e s o u r c e I d
	Populate existing agrFund rows with a FwkResourceID.  This will involve seeding the existing
	agrFund rows with a FwkResourceID and adding a corresponding row in the FwkResource table.
	*/
	-- Get the maximum Resource ID that currently exists.
	declare @maxFwkResourceId int
	select @maxFwkResourceId = max(id)+1 from dbo.fwkResource (nolock)
	
	-- Drop temp table used to seed fwkresourceIDs if it already exists.
	if object_id('dbo.tempFwkResource') is not null
	Begin
		print ''
		print 'Dropping table dbo.tempFwkResource...'
		drop table dbo.tempFwkResource
	End

	-- Use dynamic SQL to create seeds for each fund and its corresponding fwkresourceid.  The reason for
	-- using dynamic is that the identity function only works with literal integer values and not variables,
	-- so dynamic is used to pass it the max starting seed value as a literal string.
	print ''
	print 'Seeding fund resource IDs into dbo.tempFwkResource table...'
	declare @strSQL varchar(1000)
	set @strSQL = 'select cast(id as int) as agrfundId,identity(int,' + cast(@maxFwkResourceId as varchar) +
					',1) as FwkResourceId into dbo.tempFwkResource from dbo.agrfund where fwkResourceId is null'
	exec(@strSQL)
	
	/*
	C r e a t e / A s s i g n  F u n d  R e s o u r c e s  t o  F u n d s  n o t  A s s i g n e d
	If there is a temp table then assign the fund resources and update them on AgrFund table. 
	*/	
	If object_id('dbo.tempFwkResource') is not null 
	Begin
		print ''
		Print 'Creating fund dbo.FwkResource rows...'
		/*
		Set identity insert on in order to insert the assigned identity values into fwkresource
		from the tempFwkResource seed table. 
		*/
		set identity_insert dbo.FwkResource on

		insert into dbo.FwkResource (Id,FwkResourceTypeId,PrimaryOrganizationId) 
		select FwkResourceId, @FundResourceTypeId, null from dbo.tempFwkResource t

		set identity_insert dbo.FwkResource off
		
		print ''
		Print 'Updating funds with the resource IDs previously created ...'

		--update each fund with its assigned fwkresourceid
		update f set fwkResourceId = t.FwkResourceId 
		from dbo.tempFwkResource t inner join dbo.agrfund f on f.id = t.agrFundId
	End

	/*
	R e p l a c e  A g r e e m e n t  D i r e c t  A c c e s s  w i t h  F u n d  D i r e c t  A c c e s s
	Users currently have direct access to only agreement resources.  This access must be replaced with
	direct access for all the funds related to those agreements.  Without this step, direct access
	would stop working. 
	*/	
	print ''
	Print 'Creating temp table of user map data, fund number and corresponding fund resource ID...'
	if object_id('tempdb..#CurrentDirectAccess') is not null drop table #CurrentDirectAccess
	select rdm.*,f.fundNumber,f.FwkResourceId as 'FundResourceId' 
	into #CurrentDirectAccess 
	from dbo.FwkResource_DomainUser_map rdm
	left outer join dbo.FwkResource r on r.id = rdm.FwkResourceId
	left outer join dbo.agragreement a on a.fwkResourceId = r.id 
	left outer join dbo.agrFund_AgrAgreement_map fam on fam.agragreementid = a.id
	left outer join dbo.agrFund f on f.id = fam.agrfundid
	where r.fwkresourcetypeid = 1 and a.iscurrent=1 and a.isamendment =0

	/*
	I n s e r t  F u n d  D i r e c t  A c c e s s  to  R e p l a c e  A g r e e m e n t  D i r e c t  A c c e s s
	Insert fund direct access for all funds related to agreements to which direct access has been granted.
	*/
	print ''
	Print 'Inserting new fund resource user map rows...'
	Insert into dbo.FwkResource_DomainUser_map(FwkDomainUserId,FwkResourceId,AccessRightBitMask,Comment)
	select t.FwkDomainUserId,t.FundResourceId,t.AccessRightBitMask,t.Comment
	from #CurrentDirectAccess t
	left outer join dbo.FwkResource_DomainUser_map rdm on t.FwkDomainUserId = rdm.FwkDomainUserId
														and t.FundResourceId = rdm.FwkResourceId
	where rdm.id is null and t.FundResourceId is not null
	/*
	U p d a t e  A g r e e m e n t / F u n d  R e s o u r c e  I D s  o n  A g r e e m e n t _ f c t
	Update agreement/fund resource IDs on all agragreement_fct rows.
	*/
	print ''
	Print 'Updating Agragreement_fct with FundFwkResourceId column resourceid and FwkResourceId data...'
	Update af set fundfwkresourceid = f.fwkResourceId 
				,fwkresourceid = a.fwkresourceid
				,resourceid = a.fwkresourceid
	from dbo.agrfund f
	inner join dbo.agragreement_fct af on af.fundnumber = f.fundnumber
	inner join dbo.agragreement a on a.id = af.agreementid
	/*
	D e l e t e  A g r e e m e n t  D i r e c t  A c c e s s
	We don't need direct access to agreements because we now do it by funds.
	*/
	print ''
	Print 'Deleting agreement resource user map rows replaced by fund resource user map rows...'
	Delete rdm from dbo.FwkResource_DomainUser_map rdm inner join #CurrentDirectAccess t on t.id = rdm.id

	print ''
	Print 'Fund Level Security Upgrade Committed...'
	Commit Transaction FundLevelSecurityDBUpgrade

	--Create index on the resourceID column.
	if not exists(SELECT OBJECT_ID FROM sys.indexes WHERE name like 'IX_AgrFund_FwkResourceId') 
	Begin
		print ''
		Print 'Creating index IX_AgrFund_FwkResourceId on dbo.AgrFund...'
		create index IX_AgrFund_FwkResourceId on dbo.AgrFund (FwkResourceId);
	End

	--Create index on the FundFwkResourceId column.

	if not exists(SELECT OBJECT_ID FROM sys.indexes WHERE name like 'IX_AgrAgreement_Fct_FundFwkResourceId')
	Begin
		print ''
		Print 'Creating index IX_AgrAgreement_Fct_FundFwkResourceId on dbo.AgrAgreement_fct...'
		create index IX_AgrAgreement_Fct_FundFwkResourceId on dbo.AgrAgreement_fct (FundFwkResourceId);
	End

end
else Begin
	print ''
	Print 'Database was already upgraded for Fund Level Security...'
End

End Try

Begin Catch
Declare 
    @ErrorMessage    nvarchar(4000),
    @ErrorNumber     int,
    @ErrorSeverity   int,
    @ErrorState      int,
    @ErrorLine       int,
    @ErrorProcedure  nvarchar(200);

-- Assign variables to error-handling functions that 
-- capture information for RAISERROR.
Select 
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original
-- error information.
Select @ErrorMessage = 
    N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
        'Message: '+ ERROR_MESSAGE();

IF XACT_STATE() <> 0 Rollback transaction FundLevelSecurityDBUpgrade

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
Raiserror 
    (
    @ErrorMessage, 
    @ErrorSeverity, 
    1,               
    @ErrorNumber,    -- parameter: original error number.
    @ErrorSeverity,  -- parameter: original error severity.
    @ErrorState,     -- parameter: original error state.
    @ErrorProcedure, -- parameter: original error procedure name.
    @ErrorLine       -- parameter: original error line number.
    );
End Catch;
